import pandas as pd
import numpy as np
import problem_2_2 as problem_2
import pro as problem_4


def doit():
    ratio = problem_2.doit() / 100
    #  读取
    file_name = '附件1 近5年402家供应商的相关数据.xlsx'
    io = pd.io.excel.ExcelFile(file_name)

    data1 = pd.read_excel(io, sheet_name=0)
    data2 = pd.read_excel(io, sheet_name=1)
    # 遍历所有行
    df1 = pd.DataFrame(data1)
    df2 = pd.DataFrame(data2)

    one = []
    two = []
    for index, row in df1.iterrows():
        tw = [row["材料分类"]]
        for i in range(240):
            i += 1
            ss = 'W'
            if i < 10:
                ss += "00" + str(i)
            elif i < 100:
                ss += "0" + str(i)
            else:
                ss += str(i)
            tw.append(int(row[ss]))
        one.append(tw)

    for index, row in df2.iterrows():
        tw = [row["材料分类"]]
        for i in range(240):
            i += 1
            ss = 'W'
            if i < 10:
                ss += "00" + str(i)
            elif i < 100:
                ss += "0" + str(i)
            else:
                ss += str(i)
            tw.append(int(row[ss]))
        two.append(tw)

    # 订单频数统计, 每一年的
    frequency = []  # 频数
    good = []
    for i in one:
        tt = []
        for j in range(1, 241):
            if i[j] > 0:
                tt.append(j)
        frequency.append(tt)
        wcc = []
        for th in range(5):
            wc = 0
            for tth in range(48 * th + 1, (th + 1) * 48 + 1):  # 1 ~ 49, 49 ~ 97, 97
                if i[tth] > 0:
                    wc += 1
            wcc.append(wc)
        good.append(wcc)

    proportion = []  # 比例
    for i in range(len(one)):
        tt = 0
        tchh = 0
        for th in range(5):
            ttc = 0.0
            for tth in range(48 * th + 1, (th + 1) * 48 + 1):
                if one[i][tth] > 0:
                    ttc += two[i][tth] / one[i][tth]
            if good[i][th] > 0:
                tt += ttc / good[i][th]
            else:
                tchh += 1
        proportion.append(tt / (5 - tchh))

    Supply_quantity = []  # 供货量
    for i in range(len(one)):
        tt = 0
        for th in range(5):
            ttc = 0.0
            for tth in range(48 * th + 1, (th + 1) * 48 + 1):
                if one[i][tth] > 0:
                    ttc += two[i][tth]
            if good[i][th] > 0:
                tt += ttc / good[i][th]
        Supply_quantity.append([tt / 5, two[i][0]])
    frequency = [int(len(i) / 10 + 0.99999) for i in frequency]  # 在未来24周该供应商出现的次数
    Supply_quantity[139][0] *= 1.9
    Supply_quantity[200][0] *= 3
    its = [i[0] for i in Supply_quantity]
    for i in range(len(Supply_quantity)):  # 供应商提供的原料/加工一立方米产品消耗该原料量
        if Supply_quantity[i][1] == 'A':
            Supply_quantity[i][0] /= 0.6
        elif Supply_quantity[i][1] == 'B':
            Supply_quantity[i][0] /= 0.66
        else:
            Supply_quantity[i][0] /= 0.72
        Supply_quantity[i][0] *= proportion[i]
        Supply_quantity[i][0] *= frequency[i]
        Supply_quantity[i][0] *= (1 - ratio)

    data = {'供货商ID': [i + 1 for i in range(len(Supply_quantity))],
            '未来24周供货商提供的原料能带生产的最大产品': [i[0] for i in Supply_quantity],
            '原料种类': [i[1] for i in Supply_quantity],
            '出现次数': frequency,
            '供应量': its,
            '比例': proportion}
    df = pd.DataFrame(data)
    df.to_excel('确定供货商.xlsx')
